﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'RegistroOTDetalleRep')
	BEGIN
		DROP  Procedure  RegistroOTDetalleRep
	END
GO 

CREATE PROCEDURE [dbo].[RegistroOTDetalleRep]
	(	
		@EmpresaId	AS INT,
		@NumeroOT	AS VARCHAR(30)
	)	
AS
BEGIN	
	SET NOCOUNT ON

	DECLARE @id AS INT
	DECLARE @OTDetalle Table
		(NumeroOT		VARCHAR(30),
		 PuestoNombre	VARCHAR(50),
		 Horas			DECIMAL(10,2),
		 Total			MONEY)


	-- Lista de tablas a ser devueltas a la aplicacion para cargar la hoja de registro	
	SELECT 0 AS TableId, 'Catalog'			AS TableName	UNION
	SELECT 1 AS TableId, 'OrdenTrabajo'		AS TableName	UNION
	SELECT 2 AS TableId, 'OTDetalle'		AS TableName	UNION
	SELECT 3 AS TableId, 'OTMaterial'		AS TableName    UNION
	SELECT 4 AS TableId, 'OTGastoDirecto'	AS TableName    UNION
	SELECT 5 AS TableId, 'OTGastoIndirecto'		AS TableName    UNION
	SELECT 6 AS TableId, 'OTMaquina'		AS TableName   -- UNION		

	SELECT @id = id 
	FROM Lit_OrdenTrabajo 
	WHERE EmpresaId = @empresaId
	  AND NumeroOT = @numeroOT

	-- OrdenTrabajo
	SELECT o.NumeroOT, e.Nombre AS EmpresaNombre, FechaInicial, FechaFinal, 
		   CASE o.Estado
				WHEN 'A' THEN 'EN PROCESO'
				WHEN 'T' THEN 'FINALIZADA'
		   END AS Estado, o.UnidadesSolicitadas As Cantidad, o.UnidadesProducidas AS CantidadProducida
	FROM lit_ordenTrabajo o
	INNER JOIN ld_general..empresas e ON o.empresaId = e.id
	WHERE o.Id = @id

	-- OT Detalle (Mano de obra)
	INSERT INTO @OTDetalle
		SELECT ot.NumeroOT, p.Nombre AS PuestoNombre, 
			SUM (ISNULL(HorasRegular,0)+ISNULL(HorasExtra,0) + ISNULL(HorasDoble,0)) AS Horas, 
			SUM( (ISNULL(HorasRegular,0)* ISNULL(CostoOperarioxHora,0))
				+(ISNULL(HorasExtra,0)*ISNULL(CostoOperarioxHora,0)*1.5) 
				+(ISNULL(HorasDoble,0)*ISNULL(CostoOperarioxHora,0)*2.0)) AS Total
		FROM Lit_OTDetalle o
		INNER JOIN Lit_OrdenTrabajo ot ON ot.Id = o.OTId
		INNER JOIN Lit_Operario op ON o.operarioId = op.id
		INNER JOIN Lit_Puesto p ON op.puestoId = p.id
		WHERE OTId = @id	  
		GROUP BY ot.NumeroOT, p.Nombre
		UNION
		SELECT ot.NumeroOT, p.Nombre AS PuestoNombre, 
			SUM(ISNULL(HorasRegular,0)+ISNULL(HorasExtra,0) + ISNULL(HorasDoble,0)) AS Horas, 
			SUM( (ISNULL(HorasRegular,0) * ISNULL(CostoAyudantexHora,0))
				+(ISNULL(HorasExtra,0)*ISNULL(CostoAyudantexHora,0)*1.5) 
				+(ISNULL(HorasDoble,0)*ISNULL(CostoAyudantexHora,0)*2.0)) AS Total
		FROM Lit_OTDetalle o
		INNER JOIN Lit_OrdenTrabajo ot ON ot.Id = o.OTId
		INNER JOIN Lit_Operario op ON o.ayudanteId = op.id
		INNER JOIN Lit_Puesto p ON op.puestoId = p.id
		WHERE OTId = @id	  
		GROUP BY ot.NumeroOT, p.Nombre
	
	SELECT NumeroOT, PuestoNombre, SUM(Horas) AS Horas, SUM(Total) AS Total
	FROM @OTDetalle
	GROUP BY NumeroOT, PuestoNombre	
	
	-- OT Material
	SELECT ot.NumeroOT, m.ArticuloId, art_nombre AS ArticuloNombre, Cantidad, CostoArticuloUnitario AS Costo, SUM(CostoArticuloTotal) AS Total
	FROM Lit_OTMaterial m
	INNER JOIN Lit_OrdenTrabajo ot ON ot.Id = m.OTId
	INNER JOIN Articulo a ON m.ArticuloId = a.Art_id
	WHERE OTId = @id
	AND a.Emp_Id = @empresaId
	GROUP BY ot.NumeroOT, m.ArticuloId, art_nombre, Cantidad, CostoArticuloUnitario

	-- OT Gastos Directos
	SELECT ot.NumeroOT, otg.GastoId, g.Concepto As GastoNombre, otg.Monto
	FROM Lit_OTGasto otg
	INNER JOIN Lit_Gasto g ON g.Id = otg.GastoId
	INNER JOIN Lit_OrdenTrabajo ot ON ot.Id = otg.OTId
	WHERE OTId = @id AND g.Tipo = 'D'

	-- OT Gastos Indirectos
	SELECT ot.NumeroOT, otg.GastoId, g.Concepto As GastoNombre, otg.Monto
	FROM Lit_OTGasto otg
	INNER JOIN Lit_Gasto g ON g.Id = otg.GastoId
	INNER JOIN Lit_OrdenTrabajo ot ON ot.Id = otg.OTId
	WHERE OTId = @id AND g.Tipo = 'I'
	UNION
	SELECT NumeroOT, 0 As GastoId, 'Mano de Obra Indirecta' As GastoNombre, ISNULL(ManoObraIndirecta,0)
	FROM Lit_OrdenTrabajo
	WHERE Id = @id
	AND ManoObraIndirecta IS NOT NULL
	AND ManoObraIndirecta > 0

	-- OT Maquina
	SELECT ot.NumeroOT, o.MaquinaId, m.Nombre AS MaquinaNombre, SUM(ISNULL(HorasRegular,0)+ISNULL(HorasExtra,0) + ISNULL(HorasDoble,0)) AS Horas, o.CostoMaquinaxHora, SUM(CostoMaquina) AS Total
	FROM Lit_OTDetalle o
	INNER JOIN Lit_OrdenTrabajo ot ON ot.Id = o.OTId
	INNER JOIN Lit_Maquina m ON o.MaquinaId = m.Id
	WHERE OTId = @id	  
	GROUP BY ot.NumeroOT, o.MaquinaId, m.Nombre, o.CostoMaquinaxHora
           	            
	SET NOCOUNT OFF
END
GO